package com.gitee.xtyihj.dao;

import com.gitee.xtyihj.bean.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SQLManager
{
    private static SQLManager instance = null;
    private static Connection connection = null;

    public static SQLManager get()
    {
        return instance == null ? instance = new SQLManager() : instance;
    }

    public void connectDB()
    {
        try
        {
            Class.forName("com.mysql.cj.jdbc.Driver");
            this.connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/whlg?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC","root","3314");
        }
        catch (SQLException | ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }

    public void shutdown()
    {
        try
        {
            connection.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 查询所有用户信息
     * @return List<User>
     */
    public List<User> findAllUser()
    {
        try
        {
            List<User> userList = new ArrayList<>();
            String s = SQLCommand.selectAllUser.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ResultSet resultSet = ps.executeQuery();
            while(resultSet.next())
            {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUserName(resultSet.getString("userName"));
                user.setPassWord(resultSet.getString("password"));
                userList.add(user);
            }
            resultSet.close();
            ps.close();
            return userList;
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据id查询用户信息
     * @param id 用户id
     * @return User
     */
    public User findUserByID(int id)
    {
        try
        {
            String s = SQLCommand.selectUserByID.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setInt(1,id);
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.next())
            {
                User user = new User(resultSet.getInt("id"),resultSet.getString("userName"),resultSet.getString("password"));
                resultSet.close();
                ps.close();
                return user;
            }
            else
            {
                return null;
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 根据userName和passWord查询用户信息，进行登录验证
     * @param userName 用户名
     * @param passWord 密码
     * @return boolean
     */
    public boolean hasUser(String userName,String passWord)
    {
        try
        {
            String s = SQLCommand.selectUserByUserNamePassWord.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setString(1,userName);
            ps.setString(2,passWord);
            ResultSet resultSet = ps.executeQuery();
            if(resultSet.next())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        return false;
    }
    /**
     * 根据id删除用户信息
     * @param id 用户id
     * @return void
     */
    public void deleteUserByID(int id)
    {
        try
        {
            String s = SQLCommand.deleteUserByID.commandToString();
            PreparedStatement ps = connection.prepareStatement(s);
            ps.setInt(1,id);
            ps.executeUpdate();
            ps.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
    }
}
